﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Dqaqs.Data
{
	public static class Crud
	{
		public static string UploadData(string[] header, SchemaTable tbl, IList<string> lines,
			Guid projectId, int dataSourceId, string targetTable, Schema globalSchema)
		{
			//Check the correctness of header
			for (var i = 0; i < header.Length; i++)
			{
				var h = header[i].Split("(".ToCharArray()).FirstOrDefault() ?? header[i];
				if (
					!h.Replace(" ", "").Equals(tbl.Columns[i].Name.Replace(" ", ""),
													   StringComparison.InvariantCultureIgnoreCase))
					throw new InvalidOperationException(
						string.Format("Error reading data. Line 1, Column {0}: Expected {1}, but {2} was found.", i + 1,
									  tbl.Columns[i].Name, header[i]));
			}

			//Import to database in bunches of 1000
			const int bunchSize = 10;
			using (var d = DataConatiner.New())
			{
				foreach (var bunch in GetBunch(lines, bunchSize))
				{
					var bXml = CsvToXml(bunch, targetTable, globalSchema);
					d.InsertBulk(projectId, dataSourceId, targetTable, bXml);
				}
			}
			return String.Format("{0}_{1}_{2}", projectId, dataSourceId, targetTable);
		}

		private static string CsvToXml(IEnumerable<string> bunch, string table, Schema schema)
		{
			var typeTrans = new Dictionary<string, string>
			                	{
			                		{"integer", "int"},
			                		{"string", "nvarchar(1024)"},
			                		{"datetime", "datetime"}
			                	};
			var rv = new StringBuilder("<data>");
			var cols = schema.Tables.First(t => t.Name.Equals(table, StringComparison.InvariantCultureIgnoreCase)).Columns;
			var firstLine = true;
			foreach (var line in bunch)
			{
				var fields = line.Split(",".ToCharArray());
				rv.Append("<r>");
				for (var i = 0; i < cols.Length; i++)
				{
					rv.AppendFormat(@"<{0}{1}{2}>{3}</{0}>", cols[i].Name,
									firstLine ? " t=\"" : "",
									firstLine ? typeTrans[cols[i].Type] + "\"" : "",
									fields[i]);
				}
				rv.Append("</r>");
				firstLine = false;
			}
			rv.Append("</data>");
			return rv.ToString();
		}

		private static IEnumerable<IEnumerable<string>> GetBunch(IList<string> lines, int cnt)
		{
			var i = 0;
			var c = 0;
			var rv = new List<string>();
			while (i < lines.Count)
			{
				if (i == 0) //Skip the first line
				{
					i++;
					continue;
				}
				if (c >= cnt)
				{
					c = 0;
					yield return rv;
					rv = new List<string>();
				}
				rv.Add(lines[i]);
				i++;
				c++;
			}
			yield return rv;
		}

		public static DataTable RunQuery(string query)
		{
			using (var conn = new SqlConnection(DataConatiner.ConnectionString))
			{
				conn.Open();
				var cmd = new SqlCommand(query, conn);
				var rdr = cmd.ExecuteReader();
				var rv = new DataTable();
				rv.Load(rdr);
				return rv;
			}
		}

		public static void CreateTable(Guid projectId, int dataSourceId, string tableName, string schema)
		{
			using (var d = DataConatiner.New())
			{
				d.CreateTable(projectId, dataSourceId, tableName, schema);
			}
		}
	}
}
